import os
import random

from dotenv import load_dotenv

from ..models.textbook_model import Textbook
from sqlalchemy.orm import Session
from sqlalchemy import select, update
from fastapi import HTTPException
from sqlalchemy import desc
from ..utils.photo_tools import get_image_as_base64
from ..utils.uploads_oss import main as upload_on_oss
from ..utils.deletes_oss import main as delete_on_oss
from ..utils.file_tools import delete_file

load_dotenv()
SOURCES_PATH = os.getenv("SOURCES_PATH")

def get_all_textbooks(db: Session):
    query = select(Textbook)
    textbooks = db.execute(query).scalars().all()
    return[textbook.as_dict() for textbook in textbooks]

def create_textbook(db: Session, name, path,type,subject, grade,cover,starts, oss_url=None):
    if db.execute(select(Textbook).where(Textbook.name ==name)).scalars().first():
        raise HTTPException(status_code=400, detail=f"Textbook {name} already exists")
    file_path = os.path.join(SOURCES_PATH, path)
    upload_on_oss(path,file_path)
    new_textbook=Textbook(name=name, path=path,type=type,subject=subject,
                          grade=grade,cover=cover,starts=starts,
                          oss_url="http://whatteam1.oss-cn-beijing.aliyuncs.com/"+path)
    db.add(new_textbook)
    db.commit()
    db.refresh(new_textbook)
    return new_textbook.as_dict()


def update_textbook(db: Session, textbook_id: int, subject: str = None, grade: str = None):

    # 查询教科书是否存在
    exist_textbook = db.execute(select(Textbook).where(Textbook.id == textbook_id)).scalar()
    if not exist_textbook:
        raise HTTPException(status_code=404, detail=f"Textbook {textbook_id} not found")

    # 更新字段（如果提供了新值）
    if subject is not None:
        exist_textbook.subject = subject
    if grade is not None:
        exist_textbook.grade = grade

    # 提交更改并刷新对象
    db.commit()
    db.refresh(exist_textbook)

    return exist_textbook.as_dict()


def delete_textbook(db: Session, textbook_id):
    exist_textbook = db.get(Textbook, textbook_id)
    if not exist_textbook:
        raise HTTPException(status_code=404, detail="Textbook not found")
    delete_file(exist_textbook.path)
    db.delete(exist_textbook)
    delete_on_oss(exist_textbook.path)
    db.commit()
    return exist_textbook.as_dict()


def check_textbook_exists(db: Session, textbook_id):
    return db.get(Textbook, textbook_id) is not None


def get_textbook_page_subject(db:Session,subject: str, page: int = 1, page_size: int = 10):
    try:
        offset = (page - 1) * page_size
        textbook_list = (
            db.query(Textbook)
            .filter(Textbook.subject == subject)
            # .order_by(desc(Textbook.starts))  # 按 starts 字段降序排序
            .offset(offset)
            .limit(page_size)
            .all()
        )
        return textbook_list
    finally:
        db.close()

def get_textbook_page_grade(db:Session,grade: str, page: int = 1, page_size: int = 10):
    try:
        offset = (page - 1) * page_size
        textbook_list = (
            db.query(Textbook)
            .filter(Textbook.grade== grade)
            # .order_by(desc(Textbook.starts))  # 按 starts 字段降序排序
            .offset(offset)
            .limit(page_size)
            .all()
        )
        return textbook_list
    finally:
        db.close()

def get_textbook_page_subject_grade(db:Session,subject: str,grade: str, page: int = 1, page_size: int = 10):
    try:
        offset = (page - 1) * page_size
        textbook_list = (
            db.query(Textbook)
            .filter(Textbook.subject == subject)
            .filter(Textbook.grade== grade)
            # .order_by(desc(Textbook.starts))  # 按 starts 字段降序排序
            .offset(offset)
            .limit(page_size)
            .all()
        )
        return textbook_list
    finally:
        db.close()

def get_textbook_by_id(db: Session, textbook_id: int):
    textbook = db.query(Textbook).filter(Textbook.id == textbook_id).first()

    if not textbook:
        return None

    data = textbook.as_dict()
    # if data.get("cover"):
    #     cover_base64 = get_image_as_base64(data["cover"])
    #     data["cover"] = cover_base64 or data["cover"]  # 如果找不到图片保留原始路径

    return data

def get_textbook_quantity(db: Session):
    return db.query(Textbook).count()

if __name__ == "__main__":
    from database.db import SessionLocal
    db = SessionLocal()

    a=get_textbook_page_grade(db,grade="八年级",page=1,page_size=10)
    for i in a:
        print(i.as_dict())